﻿using System.Collections.Generic;
using PtsNames = Citi.PTSConsolidation.Domain.EntrepotDatabaseNames.BookOfWork;
using System;
using System.Text;
using System.Linq;

namespace Citi.PTSConsolidation.Domain
{
    public class BookOfWork
    {
        #region Mappings

        public static readonly Dictionary<string, string> BookOfWorkColumnMappings = new Dictionary<string, string>
        {
            {PtsNames.PeriodYear, "PeriodYear"},
            {PtsNames.AccountLevelId, "FinancialTypeId"},
            {PtsNames.InitiativeId, "InitiativeId"},
            {PtsNames.InitiativeName, "InitiativeName"},
            {PtsNames.ProgramId, "ProgramId"},
            {PtsNames.ProgramName, "ProgramName"},
            {PtsNames.ProjectId, "ProjectId"},
            {PtsNames.ProjectName, "ProjectName"},
            {PtsNames.ProgramManager, "ProgramManager"},
            {PtsNames.ProgramStartDate, "ProgramStartDate"},
            {PtsNames.ProgramEndDate, "ProgramEndDate"},
            {PtsNames.ProgramTechnologyRollUp, "ProgramTechnologyRollUp"},
            {PtsNames.ProjectStatus, "ProjectStatus"},
            {PtsNames.ProjectStartDate, "ProjectStartDate"},
            {PtsNames.ProjectEndDate, "ProjectEndDate"},
            {PtsNames.ProjectManager, "ProjectManager"},
            {PtsNames.BillingClientGocCode, "BillingClientGocCode"},
            {PtsNames.Client3Name, "Client3Name"},
            {PtsNames.Client4Name, "Client4Name"},
            {PtsNames.Client5Name, "Client5Name"},
            {PtsNames.Client6Name, "Client6Name"},
            {PtsNames.Client7Name, "Client7Name"},
            {PtsNames.Client8Name, "Client8Name"},
            {PtsNames.Client9Name, "Client9Name"},
            {PtsNames.Client10Name, "Client10Name"},
            {PtsNames.ClientGocCode, "ClientGocCode"},
            {PtsNames.ClientRegion, "ClientRegion"},
            {PtsNames.System5Name, "System5Name"},
            {PtsNames.System6Name, "System6Name"},
            {PtsNames.System7Name, "System7Name"},
            {PtsNames.System8Name, "System8Name"},
            {PtsNames.System9Name, "System9Name"},
            {PtsNames.System10Name, "System10Name"},
            {PtsNames.SystemGocCode, "SystemGOCCode"},
            {PtsNames.SystemRegion, "SystemRegion"},
            {PtsNames.Lob, "LOB"},
            {PtsNames.RuntheBankBuildTheBank, "RuntheBankBuildtheBank"},
            {PtsNames.ProgramProjectLinkIndicator, "ProgramProjectLinkIndicator"},
            {PtsNames.WorkTypeFinancials, "WorkTypeFinancials"},
            {PtsNames.InitiativeInvestimentCategory, "InitiativeInvestimentCategory"},
            {PtsNames.ProgramInvestimentCategory, "ProgramInvestimentCategory"},
            {PtsNames.ProgramCapitalizedIndicator, "ProgramCapitalizedIndicator"},
            {PtsNames.ProgramStatus, "ProgramStatus"},
            {PtsNames.ProjectCapitalization, "ProjectCapitalization"},
            {PtsNames.BowFlag, "BowFlag"},
            {PtsNames.BowFlag2, "BowFlag2"},
            {PtsNames.ProjectType, "ProjectType"},
            {PtsNames.ProgramCarryoverIndicator, "ProgramCarryoverIndicator"},
            {PtsNames.TaxonomyName, "TaxonomyName"},
            {PtsNames.CuRate, "CURate"},
            {PtsNames.FirstOfProgramDesc, "FirstOfProgramDesc"},
            {PtsNames.LobParent, "LobParent"},
            {PtsNames.AdjustedFlag, "AdjustedFlag"},
            {PtsNames.January, "January"},
            {PtsNames.February, "February"},
            {PtsNames.March, "March"},
            {PtsNames.April, "April"},
            {PtsNames.May, "May"},
            {PtsNames.June, "June"},
            {PtsNames.July, "July"},
            {PtsNames.August, "August"},
            {PtsNames.September, "September"},
            {PtsNames.October, "October"},
            {PtsNames.November, "November"},
            {PtsNames.December, "December"},
            {PtsNames.YearToDate, "YearToDate"},
            {PtsNames.FullYear, "FullYear"},
            {"ReferenceDate", "ReferenceDate"},
            {"LastUpdate", "LastUpdate"}
        };

        #endregion

        #region Query Strings

        public static string GetEntrepotQueryString(string tableName)
        {
            var query = new StringBuilder();

            foreach (var mapping in BookOfWorkColumnMappings.Where(x => !new string[2] { "ReferenceDate", "LastUpdate" }.Contains(x.Key)))
                query.Append(mapping.Key).AppendLine(", ");

            query.Append("'").Append(DateTime.Today.ToString("yyyy-MM-dd")).Append("' as ReferenceDate").AppendLine(", ");
            query.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' as LastUpdate");

            return string.Format("SELECT {0} FROM {1} NOLOCK WHERE {2} = 'LAD' OR {3} = 'LAD'",
                query, tableName, PtsNames.SystemRegion, PtsNames.ClientRegion);
        }

        public static string GetQueryCreateTableNewYear(int year, bool history)
        {
            return string.Format(@"IF NOT EXISTS (SELECT 1 from [Pts{1}].[dbo].[sysobjects] where name='BookOfWork{1}{0}' AND xtype='U')
                BEGIN
	                CREATE TABLE [Pts{1}].[dbo].[BookOfWork{1}{0}] (
		                [PeriodYear]                    INT              NULL,
		                [AccountLevel1]                 VARCHAR (25)     NULL,
		                [AccountLevel2]                 VARCHAR (25)     NULL,
		                [AccountLevel3]                 VARCHAR (25)     NULL,
		                [InitiativeId]                  VARCHAR (30)     NULL,
		                [InitiativeName]                VARCHAR (255)    NULL,
		                [ProgramId]                     VARCHAR (30)     NULL,
		                [ProgramName]                   VARCHAR (255)    NULL,
		                [ProjectId]                     VARCHAR (20)     NULL,
		                [ProjectName]                   VARCHAR (200)    NULL,
		                [ProgramManager]                VARCHAR (255)    NULL,
		                [ProgramStartDate]              DATETIME         NULL,
		                [ProgramEndDate]                DATETIME         NULL,
		                [ProjectStatus]                 VARCHAR (100)    NULL,
		                [ProjectStartDate]              DATETIME         NULL,
		                [ProjectEndDate]                DATETIME         NULL,
		                [ProjectManager]                VARCHAR (150)    NULL,
		                [BillingClientGocCode]          VARCHAR (15)     NULL,
		                [Client3Name]                   VARCHAR (120)    NULL,
		                [Client4Name]                   VARCHAR (120)    NULL,
		                [Client5Name]                   VARCHAR (120)    NULL,
		                [Client6Name]                   VARCHAR (120)    NULL,
		                [Client7Name]                   VARCHAR (120)    NULL,
		                [Client8Name]                   VARCHAR (120)    NULL,
		                [Client9Name]                   VARCHAR (120)    NULL,
		                [Client10Name]                  VARCHAR (120)    NULL,
		                [ClientGocCode]                 VARCHAR (15)     NULL,
		                [ClientRegion]                  VARCHAR (64)     NULL,
		                [System5Name]                   VARCHAR (120)    NULL,
		                [System6Name]                   VARCHAR (120)    NULL,
		                [System7Name]                   VARCHAR (120)    NULL,
		                [System8Name]                   VARCHAR (120)    NULL,
		                [System9Name]                   VARCHAR (120)    NULL,
		                [System10Name]                  VARCHAR (120)    NULL,
		                [SystemGOCCode]                 VARCHAR (15)     NULL,
		                [SystemRegion]                  VARCHAR (64)     NULL,
		                [LOB]                           VARCHAR (250)    NULL,
		                [RuntheBankBuildtheBank]        VARCHAR (1)      NULL,
		                [ProgramProjectLinkIndicator]   VARCHAR (10)     NULL,
		                [WorkTypeFinancials]            VARCHAR (18)     NULL,
		                [InitiativeInvestimentCategory] VARCHAR (186)    NULL,
		                [ProgramInvestimentCategory]    VARCHAR (100)    NULL,
		                [ProgramCapitalizedIndicator]   VARCHAR (1)      NULL,
		                [ProgramStatus]                 VARCHAR (50)     NULL,
		                [ProjectCapitalization]         VARCHAR (15)     NULL,
		                [BowFlag]                       VARCHAR (10)     NULL,
		                [BowFlag2]                      VARCHAR (10)     NULL,
		                [ProjectType]                   VARCHAR (100)    NULL,
		                [ProgramCarryoverIndicator]     VARCHAR (1)      NULL,
		                [TaxonomyName]                  VARCHAR (100)    NULL,
		                [CURate]                        DECIMAL (20, 10) NULL,
		                [FirstOfProgramDesc]            VARCHAR (2000)   NULL,
		                [AdjustedFlag]                  VARCHAR (10)     NULL,
		                [LobParent]                     VARCHAR (250)    NULL,
		                [January]                       DECIMAL (38, 18) NULL,
		                [February]                      DECIMAL (38, 18) NULL,
		                [March]                         DECIMAL (38, 18) NULL,
		                [April]                         DECIMAL (38, 18) NULL,
		                [May]                           DECIMAL (38, 18) NULL,
		                [June]                          DECIMAL (38, 18) NULL,
		                [July]                          DECIMAL (38, 18) NULL,
		                [August]                        DECIMAL (38, 18) NULL,
		                [September]                     DECIMAL (38, 18) NULL,
		                [October]                       DECIMAL (38, 18) NULL,
		                [November]                      DECIMAL (38, 18) NULL,
		                [December]                      DECIMAL (38, 18) NULL,
		                [YearToDate]                    DECIMAL (38, 18) NULL,
		                [FullYear]                      DECIMAL (38, 18) NULL,
		                [ReferenceDate]                 DATE             DEFAULT (getdate()) NOT NULL,
		                [LastUpdate]                    DATETIME         DEFAULT (getdate()) NOT NULL,
                        [ClientAllocation]	            DECIMAL (20, 4)  NULL,
                        [ProjectAccount1Sum]            DECIMAL (38, 18) NULL,
                        [ProjectAccount1SystemGocSum]   DECIMAL (38, 18) NULL,
                        [ProjectClientGocSum]           DECIMAL (38, 18) NULL,
                        [ProgramTechnologyRollUp]       VARCHAR(250) NULL,
                        [FinancialTypeId]               INT NULL

                        FOREIGN KEY (FinancialTypeId) REFERENCES FinancialType(Id)
                );
                END", year, history ? "History" : string.Empty);
        }

        public static string GetSystemDailyVarianceReportQuery(int year, DateTime dateCurrent)
        {
            return string.Format("[spReportSystemVariance] {0}, '{1}'", year - dateCurrent.Year, dateCurrent.ToString("yyyy-MM-dd"));
        }

        public static string GetSumProjectAccount1UpdateQuery(int year, DateTime date)
        {
            return string.Format(@"UPDATE BookOfWork{0} 
                            SET ProjectAccount1Sum = 
	                            (SELECT SUM(FullYear)
	                            FROM BookOfWork{0} currentBow
                                    INNER JOIN FinancialType currentFinancialType on currentBow.FinancialTypeId = currentFinancialType.Id
	                            WHERE BookOfWork{0}.ReferenceDate = currentBow.ReferenceDate
		                            AND BookOfWork{0}.ProgramId = currentBow.ProgramId
		                            AND BookOfWork{0}.ProjectId = currentBow.ProjectId
		                            AND FinancialType.AccountLevel1 = currentFinancialType.AccountLevel1)
                            FROM BookOfWork{0}
                                INNER JOIN FinancialType on BookOfWork{0}.FinancialTypeId = FinancialType.Id
                            WHERE ReferenceDate = '{1}'", year, date.ToString("yyyy-MM-dd"));
        }

        public static string GetSumProjectAccount1SystemGOCUpdateQuery(int year, DateTime date)
        {
            return string.Format(@"UPDATE BookOfWork{0} 
                            SET ProjectAccount1SystemGOCSum = 
	                            (SELECT SUM(FullYear)
	                            FROM BookOfWork{0} currentBow
                                    INNER JOIN FinancialType currentFinancialType on currentBow.FinancialTypeId = currentFinancialType.Id
	                            WHERE BookOfWork{0}.ReferenceDate = currentBow.ReferenceDate
		                            AND BookOfWork{0}.ProgramId = currentBow.ProgramId
		                            AND BookOfWork{0}.ProjectId = currentBow.ProjectId
		                            AND FinancialType.AccountLevel1 = currentFinancialType.AccountLevel1
                                    AND BookOfWork{0}.SystemGOCCode = currentBow.SystemGOCCode)
                            FROM BookOfWork{0}
                                INNER JOIN FinancialType on BookOfWork{0}.FinancialTypeId = FinancialType.Id
                            WHERE ReferenceDate = '{1}'", year, date.ToString("yyyy-MM-dd"));
        }

        public static string GetSaveSystemVarianceQuery(int year, DateTime date)
        {
            return GetVarianceQuery("spSaveSystemVariance", year, date);
        }

        public static string GetSaveClientVarianceQuery(int year, DateTime date)
        {
            return GetVarianceQuery("spSaveClientVariance", year, date);
        }

        private static string GetVarianceQuery(string procedureName, int year, DateTime date)
        {
            var monday = date.AddDays(-(((int)date.DayOfWeek) - 1));

            return string.Format(@"EXEC {0} {1}, '{2}', '{3}'", procedureName, year, date.ToString("yyyy-MM-dd"), monday.ToString("yyyy-MM-dd"));
        }

        public static string GetSumProjectClientGOCUpdateQuery(int year, DateTime date)
        {
            return string.Format(@"UPDATE BookOfWork{0} 
                            SET [ProjectClientGocSum] = 
	                            (SELECT SUM(FullYear)
	                            FROM BookOfWork{0} currentBow
	                            WHERE BookOfWork{0}.ReferenceDate = currentBow.ReferenceDate
		                            AND BookOfWork{0}.ProgramId = currentBow.ProgramId
		                            AND BookOfWork{0}.ProjectId = currentBow.ProjectId
                                    AND BookOfWork{0}.ClientGOCCode = currentBow.ClientGOCCode)
                            WHERE ReferenceDate = '{1}'", year, date.ToString("yyyy-MM-dd"));
        }

        public static string GetClientDailyVarianceReportQuery(int year, DateTime dateCurrent)
        {
            return string.Format("[spReportClientVariance] {0}, '{1}'", year - dateCurrent.Year, dateCurrent.ToString("yyyy-MM-dd"));
        }

        #endregion
    }
}
